from datetime import datetime, timedelta, date
from initdatabase import *
from sqlalchemy import create_engine, Column, Integer, String, MetaData, ForeignKey, and_, or_
import pandas as pd
from sqlalchemy import create_engine, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base
from urllib.parse import quote_plus as urlquote

####################################################### 全局变量 ########################################################
#原始字典的位置：用于中英文名的相互映射
yuanshi_dict_path = 'D:/sibaoproject_文档/各种表以及任务分配10.22/1数据字典从0104开始更新/数据库原始数据字典_0106更新.xlsx' #前端修改
shuchu_dict_path = 'D:/sibaoproject_文档/各种表以及任务分配10.22/1数据字典从0104开始更新/加工报表数据字典_0106更新.xlsx' #前端修改


#数据库连接
userName = 'root'
password = '@6x!v2M,7)8eDaNion'
dbHost = '39.100.113.99'
dbPort = 3306
dbName = 'jietingtest'
DB_CONNECT = f'mysql://{userName}:{urlquote(password)}@{dbHost}:{dbPort}/{dbName}?charset=utf8mb4'



####################################################### 公共函数定义 ########################################################
# 构建连接数据库的session
def get_session():
    '''创建数据库连接和会话'''
    engine = create_engine(DB_CONNECT)
    Session = sessionmaker(bind=engine)
    session = Session()
    return session

#表字段中文名→英文名
def upload_field_corr(upload_df, table_zh_name):
    table_en_name=pd.read_excel(yuanshi_dict_path, table_zh_name, header=None).iloc[0,0]
    base_name_df = pd.read_excel(yuanshi_dict_path, table_zh_name, header=1)
    # 创建从中文名到英文名的映射字典
    column_name_map = base_name_df.set_index('释义')['字段名'].to_dict()
    # 使用映射字典重命名upload_df的列名，中文名（释义）→英文名（字段名）
    for old_col, new_col in column_name_map.items():
        if old_col in upload_df.columns:
            upload_df.rename(columns={old_col: new_col}, inplace=True)
    return upload_df

#表字段英文名→中文名
def uploaded_field_corr_entozh(upload_df, table_zh_name):
    table_en_name=pd.read_excel(yuanshi_dict_path, table_zh_name, header=None).iloc[0,0]
    base_name_df = pd.read_excel(yuanshi_dict_path, table_zh_name, header=1)
    # 创建从中文名到英文名的映射字典
    column_name_map = base_name_df.set_index('字段名')['释义'].to_dict()
    # 使用映射字典重命名upload_df的列名，中文名（释义）→英文名（字段名）
    for old_col, new_col in column_name_map.items():
        if old_col in upload_df.columns:
            upload_df.rename(columns={old_col: new_col}, inplace=True)
    return upload_df

#加工报表表字段英文名→中文名
def uploaded_field_corr_entozh_res(upload_df, table_zh_name):
    table_en_name=pd.read_excel(shuchu_dict_path, table_zh_name, header=None).iloc[0,0]
    base_name_df = pd.read_excel(shuchu_dict_path, table_zh_name, header=1)
    # 创建从中文名到英文名的映射字典
    column_name_map = base_name_df.set_index('字段名')['释义'].to_dict()
    column_name_map["row_count"] = "数据记录数量"
    # 使用映射字典重命名upload_df的列名，中文名（释义）→英文名（字段名）
    for old_col, new_col in column_name_map.items():
        if old_col in upload_df.columns:
            upload_df.rename(columns={old_col: new_col}, inplace=True)
    return upload_df


#按stat_time刷新插入数据
def refresh_insert(session, df: pd.DataFrame, class_name, target_date):
    # 检测想插入的target_date是否在数据库里面有东西，有就删了
    # 由此达到刷新数据的效果
    session.query(class_name).filter(class_name.stat_time == target_date).delete()
    # 转换数据为字典
    df = df.to_dict(orient='records')
    # 批量插入
    objects = [class_name(**eachline) for eachline in df]
    session.add_all(objects)
    session.commit()
    # 关闭会话
    session.close()


'''—————————————————————————————方法：规范化输出—————————————————————————————'''
# 定义处理函数
def format_number(value):
    if isinstance(value, int):
        return str(value)
    elif isinstance(value, float):
        if abs(value) > 0.1:
            if value % 1 != 0:
                return str("{:.2f}".format(value))  # 大于0.1保留两位小数
            else:
                return int(value)
        else:
            if value == 0:
                return str(0)
            else:
                return str("{:.2%}".format(value))  # 小于等于0.1转化为百分数
    else:
        return value
    pass
